package fr.cie.blog.modeldao.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import fr.cie.blog.beans.Article;
import fr.cie.blog.beans.Category;
import fr.cie.blog.beans.Comment;
import fr.cie.blog.beans.User;
import fr.cie.blog.domainservice.IServiceArticle;
import fr.cie.blog.modeldao.services.GestionBaseDeDonnees;
import fr.cie.blog.modeldao.services.Mapper;

/**
 * @author AetM
 * 
 */
public class ArticleDaoImplSql extends ModelDAO implements IServiceArticle {

	/**
	 * mapper.
	 */
	private Mapper mapper = new Mapper();

	/**
	 * Log4J the logger.
	 */
	private static final Logger LOG = Logger.getLogger(ArticleDaoImplSql.class);

	// Properties Sql

	/**
	 * connection.
	 */
	private Connection connection = null;

	/**
	 * result.
	 */
	private ResultSet result = null;

	/**
	 * article.
	 */
	private Article article;

	/**
	 * articleList.
	 */
	private List<Article> articleList;

	/*
	 * (non-Javadoc)
	 * 
	 * @see fr.cie.blog.domainservice.IServiceGeneric#save(java.lang.Object)
	 */
	@Override
	public final int save(final Article pArticle) {
		// TODO Auto-generated method stub
		return 0;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see fr.cie.blog.domainservice.IServiceGeneric#delete(java.lang.Object)
	 */
	@Override
	public final int delete(final Article pArticle) {
		// TODO Auto-generated method stub
		return 0;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see fr.cie.blog.domainservice.IServiceGeneric#findAll()
	 */
	@Override
	public final List<Article> findAll() {
		LOG.info("Entering findAll()");
		// Variables
		PreparedStatement requete = null;
		Article articleBase = null;
		User userBase = null;
		Category categoryBase = null;
		String requeteString = null;

		try {
			// Ouverture d'une connexion
			connection = super.getConnection();

			requeteString = "Select * FROM article "
					+ "LEFT JOIN user ON article.userId = user.id "
					+ "LEFT JOIN category ON article.categoryId = category.id "
					+ "ORDER BY article.publicationDate DESC";

			requete = connection.prepareStatement(requeteString);

			// Execution de la requête
			result = requete.executeQuery();

			articleList = new ArrayList<Article>();
			// On stocke le resultat dans une liste
			if (result != null) {
				while (result.next()) {

					articleBase = mapper.mapperArticle(result);
					userBase = mapper.mapperUser(result);
					
					// Mapping of the author
					userBase = mapper.mapperUser(result);

					// Links between the user and the article
					userBase.getArticleList().add(articleBase);
					articleBase.setAuthor(userBase);
					
					// Mapping of the category
					categoryBase = mapper.mapperCategory(result);

					// Links between the category and the article
					categoryBase.getArticleList().add(articleBase);
					articleBase.setCategory(categoryBase);
					
					articleList.add(articleBase);

				}

			}
		} catch (final Exception e) {
			LOG.error("ERROR fonction findAll()" + e);
		} finally {
			try {

				if (result != null) {
					GestionBaseDeDonnees.getInstance().closeResulset(result);
				}
				if (requete != null) {
					GestionBaseDeDonnees.getInstance().closeRequest(requete);
				}
				if (connection != null) {
					GestionBaseDeDonnees.getInstance().closeConnection(
							connection);
				}
			} catch (final Exception ex) {
				LOG.error("Erreur lors de la fermeture de la connexion avec "
						+ "la base de données dans la fonction findAll()" + ex);
			}
		}

		LOG.info("Exit findAll()  with articleList.size = "
				+ articleList.size());

		return articleList;

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * fr.cie.blog.domainservice.IServiceArticle#findByCategory(fr.cie.blog.
	 * beans.Category)
	 */
	@Override
	public final List<Article> findByCategory(final long categoryId) {
		LOG.info("Entering findByCategory(" + categoryId + ")");
		// Variables
		PreparedStatement requete = null;
		Article pArticle = null;

		String requeteString = null;

		try {
			// Ouverture d'une connexion
			connection = super.getConnection();

			requeteString = "SELECT * FROM article WHERE"
					+ " article.categoryId = ?";

			requete = connection.prepareStatement(requeteString);
			requete.setLong(1, categoryId);

			// Execution de la requête
			result = requete.executeQuery();

			articleList = new ArrayList<Article>();
			// On stocke le resultat dans une liste
			if (result != null) {
				while (result.next()) {

					pArticle = mapper.mapperArticle(result);

					articleList.add(pArticle);

				}

			}
		} catch (final Exception e) {
			LOG.error("ERROR fonction findByCategory" + e);
		} finally {
			try {

				if (result != null) {
					GestionBaseDeDonnees.getInstance().closeResulset(result);
				}
				if (requete != null) {
					GestionBaseDeDonnees.getInstance().closeRequest(requete);
				}
				if (connection != null) {
					GestionBaseDeDonnees.getInstance().closeConnection(
							connection);
				}
			} catch (final Exception ex) {
				LOG.error("Erreur lors de la fermeture de la connexion avec "
						+ "la base de données dans la fonction findByCategory"
						+ ex);
			}
		}

		LOG.info("Exit findByCategory  with articleList.size : "
				+ articleList.size());

		return articleList;

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * fr.cie.blog.domainservice.IServiceArticle#findByComment(fr.cie.blog.beans
	 * .Comment)
	 */
	@Override
	public final Article findByComment(final Comment pComment) {
		// TODO Auto-generated method stub
		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see fr.cie.blog.domainservice.IServiceGeneric#findById(int)
	 */
	@Override
	public final Article findById(final long pIdentifier) {
		LOG.info("Entering findById(" + pIdentifier + ")");
		// Variables
		PreparedStatement requete = null;
		Article articleBase = null;
		User userBase = null;
		String requeteString = null;

		try {
			// Ouverture d'une connexion
			connection = super.getConnection();

			requeteString = "SELECT * FROM  article LEFT JOIN comment "
					+ "ON article.id = comment.articleId "
					+ "WHERE article.id = ?";

			requete = connection.prepareStatement(requeteString);

			requete.setLong(1, pIdentifier);

			// Execution de la requête
			result = requete.executeQuery();

			// On stocke le resultat dans une liste
			if (result != null) {
				if (result.first()) {
					// On effectue le mapping des attributs avec les champs de
					// la table SQL
					articleBase = mapper.mapperArticle(result);

					// Mapping of the author
					userBase = mapper.mapperUser(result);

					// Links between the user and the article
					userBase.getArticleList().add(articleBase);
					articleBase.setAuthor(userBase);

					article = articleBase;

				}
			}
		} catch (final Exception e) {
			LOG.debug("Erreur dans la requête dans la "
					+ "classe ArticleDaoImplSql fonction findById("
					+ pIdentifier + ")" + e);
		} finally {
			try {
				// Fermeture de la connexion
				if (result != null) {
					GestionBaseDeDonnees.getInstance().closeResulset(result);
				}
				if (requete != null) {
					GestionBaseDeDonnees.getInstance().closeRequest(requete);
				}
				if (connection != null) {
					GestionBaseDeDonnees.getInstance().closeConnection(
							connection);
				}
			} catch (final Exception ex) {
				LOG.debug("Erreur lors de la fermeture de la connexion avec "
						+ "la base de données dans la classe ArticleDaoImplSql "
						+ "fonction findById(" + pIdentifier + ")" + ex);
			}
		}

		LOG.info("Exit findById(" + articleBase.getId() + ")");
		return articleBase;

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see fr.cie.blog.domainservice.IServiceArticle#get5lastArticles()
	 */
	@Override
	public final List<Article> findFiveLastArticles() {
		LOG.info("Entering get5lastArticles no param");
		// Variables
		PreparedStatement requete = null;
		Article pArticle = null;

		String requeteString = null;

		try {
			// Ouverture d'une connexion
			connection = super.getConnection();

			requeteString = "Select * FROM article ORDER "
					+ "BY article.publicationDate DESC LIMIT 0,4";

			requete = connection.prepareStatement(requeteString);

			// Execution de la requête
			result = requete.executeQuery();

			articleList = new ArrayList<Article>();
			// On stocke le resultat dans une liste
			if (result != null) {
				while (result.next()) {

					pArticle = mapper.mapperArticle(result);

					articleList.add(pArticle);

				}

			}
		} catch (final Exception e) {
			LOG.error("ERROR fonction get5lastArticles" + e);
		} finally {
			try {

				if (result != null) {
					GestionBaseDeDonnees.getInstance().closeResulset(result);
				}
				if (requete != null) {
					GestionBaseDeDonnees.getInstance().closeRequest(requete);
				}
				if (connection != null) {
					GestionBaseDeDonnees.getInstance().closeConnection(
							connection);
				}
			} catch (final Exception ex) {
				LOG.error("Erreur lors de la fermeture de la connexion avec "
						+ "la base de données dans la fonctionget5lastArticles"
						+ ex);
			}
		}

		LOG.info("Exit get5lastArticles  with articleList.size : "
				+ articleList.size());

		return articleList;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see fr.cie.blog.domainservice.IServiceGeneric#update(java.lang.Object)
	 */
	@Override
	public final int update(final Article pArticle) {
		// TODO Auto-generated method stub
		return 0;
	}

}